﻿using CC.Common;
using OpenXmlSdk;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Demo
{
    public partial class OpenXmlSdkDemo : Form
    {
        private string mHisFile = "history.his";
        private string mXlsFile;
        public OpenXmlSdkDemo()
        {
            InitializeComponent();
        }

        private void OpenXmlSdkDemo_Load(object sender, EventArgs e)
        {

        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            var path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);

            if (File.Exists(mHisFile))
            {
                MyConfiguration cfg = new MyConfiguration(mHisFile, false);
                if (cfg.ContainsKey("DataPath"))
                {
                    path = cfg.ReadString("DataPath");
                }
            }
            OpenFileDialog fd = new OpenFileDialog()
            {
                Title = "指定数据文件",
                InitialDirectory = path,
                RestoreDirectory = true,
                Multiselect = false,
                Filter = "Excel File(*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm|全部文件(*.*)|*.*",

            };
            if (fd.ShowDialog() == DialogResult.OK)
            {

                path = Path.GetDirectoryName(fd.FileName);
                MyConfiguration cfg = new MyConfiguration(false);
                if (File.Exists(mHisFile)) cfg.Load(mHisFile);
                cfg.WriteString("DataPath", path);
                cfg.Save(mHisFile);
                mXlsFile = fd.FileName;
                //var sheets = XlsHelper.GetSheetNames(fd.FileName);
                var sheets = OpenXmlExcelReader.GetSheetNames(fd.FileName);
                cbSheet.DataSource = sheets;

            }
        }
        List<Student> students;
        private void btnGetData_Click(object sender, EventArgs e)
        {
            if (cbSheet.SelectedIndex < 0) return;
            students = OpenXmlExcelReader.ExcelToList<Student>(mXlsFile, cbSheet.Text);
            //dgvData.DataSource = students;
            var dt = OpenXmlExcelReader.ExcelToDataTable(mXlsFile, cbSheet.Text);
            if (dt != null)
            {
                dgvData.DataSource = dt;
            }
        }

        private void btnSaveAs_Click(object sender, EventArgs e)
        {
            var path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);

            if (File.Exists(mHisFile))
            {
                MyConfiguration cfg = new MyConfiguration(mHisFile, false);
                if (cfg.ContainsKey("DataPath"))
                {
                    path = cfg.ReadString("DataPath");
                }
            }
            SaveFileDialog fd = new SaveFileDialog()
            {
                Title = "指定数据文件",
                InitialDirectory = path,
                RestoreDirectory = true,
                CheckFileExists = false,
                Filter = "Excel 2007 File(*.xlsx)|*.xlsx|全部文件(*.*)|*.*",

            };
            if (fd.ShowDialog() == DialogResult.OK)
            {

                path = Path.GetDirectoryName(fd.FileName);
                MyConfiguration cfg = new MyConfiguration(false);
                if (File.Exists(mHisFile)) cfg.Load(mHisFile);
                cfg.WriteString("DataPath", path);
                cfg.Save(mHisFile);
                var fs = new FileStream(Path.Combine(path,"student-object.xlsx"), FileMode.OpenOrCreate, FileAccess.ReadWrite);
                using(OpenXMLExcelWriter xls=new OpenXMLExcelWriter(fs))
                {
                    xls.AddNewWorksheet(nameof(Student));
                    students = new List<Student>()
                    {
                        new Student(){Name="张三丰", Age=20, Gender=true, LastUpdate=DateTime.Now},
                        new Student(){Name="李四光",Age=33,Gender=false,LastUpdate= DateTime.Now }
                    };
                    xls.ListToXlsx(students);
                }
                fs.Close();
                 fs = new FileStream(Path.Combine(path, "student-table.xlsx"), FileMode.OpenOrCreate, FileAccess.ReadWrite);
                using (OpenXMLExcelWriter xls = new OpenXMLExcelWriter(fs))
                {
                    xls.AddNewWorksheet(nameof(Student));
                    var table = CreateData();
                    xls.DataTableToXlsx(table, 1, 1);
                }
                fs.Close();
                Process.Start(path);
            }
        }
        private DataTable CreateData()
        {
            DataTable table = new DataTable();
            table.Columns.Add("Name",typeof(string));
            table.Columns.Add("Age",typeof(int));
            table.Columns.Add("Gender",typeof(bool));
            table.Columns.Add("LastUpdate",typeof(DateTime));
            table.Rows.Add("张三", 18, true, "1989-6-4");
            table.Rows.Add("李四", 19, true, "1989-5-25");
            return table;
        }
    }
    class Student
    {
        [DataColumn(ColumnName ="XingMing")]
        public string Name { get; set; }
        [DataColumn(ColumnName = "NianLing")]
        public int Age { get; set; }
        public DateTime LastUpdate { get; set; }
        public bool Gender { get; set; }

    }
}
